﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: May 19 2016
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[DataConversion_EmailContacts] 
(

)
RETURNS 
@JSONtoWrite TABLE (JSONvalue nvarchar(MAX),EntityID int,JSONvalue2 nvarchar(MAX),WriteType nvarchar(100))
AS
BEGIN

	-- I only need to create, since if a student email changes, that is just a new email that should be removed in a different step
	--PRINT 'Create Student Contact Info - Email'	
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"EntityID":"-1"'
		 + ',"Email":"' + E.[E-mail] + '"'
		 + ',"EntityType":"' + 'Contact' + '"'
		 + ',"EntitySubType":"' + 'Email' + '"'
		 + ',"ShortRelationshipName":"P_Contact"' 
		 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"}' AS JSONvalue,-1 AS EntityID,'Entity' AS WriteType
	--SELECT * 
	FROM
	OPENQUERY(STUDDATA97,'SELECT StudentID,[e-mail] FROM Students WHERE NOT Students.[e-mail] Is Null') E
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS P
					ON CAST(E.StudentID AS NVARCHAR) = P.PersonID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {P_Contact}''') AS PC
					ON P.EntityID = PC.Entity1ID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Contact.Email}''') AS CE
					ON CE.EntityID = PC.Entity2ID AND CE.Email = E.[e-mail]
	WHERE CE.EntityID IS NULL
	
	--PRINT 'Remove student emails that are not in the old system'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"EntityID":"' + CAST(CE.EntityID AS NVARCHAR) + '"'
		 + ',"RequestedAction":"' + 'DeleteEntity' + '"'
		 + ',"DeleteOverride":"' + 'True' + '"'
		 + ',"EntityType":"' + 'Contact' + '"'
		 + ',"EntitySubType":"' + 'Email' + '"}' AS JSONvalue,CE.EntityID AS EntityID,'Entity' AS WriteType
	--SELECT * 
	FROM
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS P
	INNER JOIN 	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT DISTINCT
				Entity1ID,Entity2ID,ShortRelationshipName FROM {Student}''') AS ST
					ON ST.Entity1ID = P.EntityID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {P_Contact}''') AS PC
					ON P.EntityID = PC.Entity1ID
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Contact.Email}''') AS CE
					ON CE.EntityID = PC.Entity2ID
	LEFT JOIN OPENQUERY(STUDDATA97,'SELECT StudentID,[e-mail] FROM Students WHERE NOT Students.[e-mail] Is Null') E
					ON CE.Email = E.[e-mail] AND CAST(E.StudentID AS NVARCHAR) = P.PersonID
	WHERE E.StudentID IS NULL

	--PRINT 'Add Parent Emails'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"EntityID":"-1"'
		 + ',"Email":"' + E.[E-mail] + '"'
		 + ',"EntityType":"' + 'Contact' + '"'
		 + ',"EntitySubType":"' + 'Email' + '"'
		 + ',"ShortRelationshipName":"P_Contact"' 
		 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"}' AS JSONvalue,-1 AS EntityID,'Entity' AS WriteType
	--SELECT * 
	FROM
	OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail] FROM [Parent e-mail] WHERE NOT [e-mail] IS NULL') E
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS P
					ON CAST(E.ParentID+10000 AS NVARCHAR) = P.PersonID
	LEFT JOIN (SELECT * FROM OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {P_Contact} WHERE ShortRelationshipName <> "Logon"''') AS PC
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Contact.Email}''') AS CE
					ON CE.EntityID = PC.Entity2ID) PC
										ON P.EntityID = PC.Entity1ID AND PC.Email = E.[e-mail]
	WHERE PC.EntityID IS NULL
	
	--PRINT 'Remove Parent Emails that are not in the old system'
	-- This will miss emails that are missing that also have a logon relationship
	-- those logon relationships will be removed later in this process, and then when this step is repeated 
	-- the next day, this address will be deleted.
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"EntityID":"' + CAST(CE.EntityID AS NVARCHAR) + '"'
		 + ',"RequestedAction":"' + 'DeleteEntity' + '"'
		 + ',"DeleteOverride":"' + 'True' + '"'
		 + ',"EntityType":"' + 'Contact' + '"'
		 + ',"EntitySubType":"' + 'Email' + '"}' AS JSONvalue,CE.EntityID AS EntityID,'Entity' AS WriteType
	--SELECT * 
	FROM
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS P
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {P_Contact} WHERE ShortRelationshipName <> "Logon"''') AS PC
		ON P.EntityID = PC.Entity1ID
	INNER JOIN 
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Contact.Email}''') AS CE
		ON CE.EntityID = PC.Entity2ID
	LEFT JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Logon}''') AS LG
		ON LG.Entity1ID = PC.Entity1ID AND LG.Entity2ID = PC.Entity2ID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT DISTINCT
				Entity1ID,Entity2ID,ShortRelationshipName FROM {Student}''') AS ST
		ON ST.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT DISTINCT
				Entity1ID,Entity2ID,ShortRelationshipName FROM {Applied}''') AS AP
		ON AP.Entity1ID = P.EntityID
	LEFT JOIN OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail] FROM [Parent e-mail] WHERE NOT [e-mail] IS NULL') E
		ON CAST(E.ParentID+10000 AS NVARCHAR) = P.PersonID AND CE.Email = E.[e-mail]
	WHERE ST.Entity1ID IS NULL AND AP.Entity1ID IS NULL AND E.ParentID IS NULL AND LG.Entity2ID IS NULL
	
	
	--PRINT 'Remove Parent Logons that do not have email addresses in the old system.'
	-- If an entity does not exist in the old system, its login will not be removed.
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"RequestedAction":"Delete","DeleteOverride":"True","ShortRelationshipName":"Logon"' 
		 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(CE.EntityID AS NVARCHAR) + '"}' AS JSONvalue,CE.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM [{Person} WHERE Logon IS NULL]''') AS P	
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Logon}''') AS LG
		ON LG.Entity1ID = P.EntityID
	INNER JOIN 
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Contact.Email}''') AS CE
		ON LG.Entity2ID = CE.EntityID
	INNER JOIN 
	OPENQUERY(STUDDATA97,'SELECT ParentID FROM Parents') PR
		ON CAST(PR.ParentID+10000 AS NVARCHAR) = P.PersonID
	LEFT JOIN
	OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail] FROM [Parent e-mail] WHERE NOT [e-mail] IS NULL') E
		ON CAST(E.ParentID+10000 AS NVARCHAR) = P.PersonID AND CE.Email = E.[e-mail]
	WHERE E.ParentID IS NULL

	----PRINT 'Add Logon relationships that are in the old database and not in the new database.'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"ShortRelationshipName":"Logon"' 
		 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
		 + ',"Entity2ID":"' + CAST(CE.EntityID AS NVARCHAR) + '"}' AS JSONvalue,CE.EntityID AS EntityID,'EntityRelationship' AS WriteType
	--SELECT * 
	FROM
	OPENQUERY(STUDDATA97,'SELECT ParentID,[e-mail] FROM [Parent e-mail] WHERE NOT [e-mail] IS NULL AND Logon = -1') E
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM [{Person} WHERE Logon IS NULL]''') AS P	
		ON CAST(E.ParentID+10000 AS NVARCHAR) = P.PersonID
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {P_Contact} WHERE ShortRelationshipName <> "Logon"''') AS PC
		ON PC.Entity1ID = P.EntityID
	INNER JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Contact.Email}''') AS CE
		ON PC.Entity2ID = CE.EntityID AND CE.Email = E.[e-mail]
	LEFT JOIN
	OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Logon}''') AS LG
		ON PC.Entity1ID = LG.Entity1ID AND PC.Entity2ID = LG.Entity2ID
	WHERE LG.Entity2ID IS NULL
			
	---- ONE TIME -- 
	---- Add logons to the P_Contact entity
	--INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	--SELECT DISTINCT '{"ShortRelationshipName":"Logon"' 
	--	 + ',"Entity1ID":"' + CAST(P.EntityID AS NVARCHAR) + '"'
	--	 + ',"Entity2ID":"' + CAST(CE2.EntityID AS NVARCHAR) + '"}' AS JSONvalue,CE.EntityID AS EntityID,'EntityRelationship' AS WriteType
	----SELECT * 
	--FROM
	--OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM [{Person} WHERE Logon IS NULL]''') AS P	
	--INNER JOIN
	--OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Logon}''') AS LG
	--	ON LG.Entity1ID = P.EntityID
	--INNER JOIN 
	--OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Contact.Email}''') AS CE
	--	ON LG.Entity2ID = CE.EntityID
	--INNER JOIN
	--OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {P_Contact} WHERE ShortRelationshipName <> "Logon"''') AS PC
	--	ON PC.Entity1ID = LG.Entity1ID
	--INNER JOIN
	--OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Contact.Email}''') AS CE2
	--	ON PC.Entity2ID = CE2.EntityID AND CE.Email = CE2.Email
		
	---- ONE TIME
	---- Remove Logon entities that do not have a P_Contact relationship
	--INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	--SELECT '{"EntityID":"' + CAST(CE.EntityID AS NVARCHAR) + '"'
	--	 + ',"RequestedAction":"' + 'DeleteEntity' + '"'
	--	 + ',"DeleteOverride":"' + 'True' + '"'
	--	 + ',"EntityType":"' + 'Contact' + '"'
	--	 + ',"EntitySubType":"' + 'Email' + '"}' AS JSONvalue,CE.EntityID AS EntityID,'Entity' AS WriteType
	----SELECT * 
	--FROM
	--OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM [{Person} WHERE Logon IS NULL]''') AS P	
	--INNER JOIN
	--OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Logon}''') AS LG
	--	ON LG.Entity1ID = P.EntityID
	--INNER JOIN 
	--OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Contact.Email}''') AS CE
	--	ON LG.Entity2ID = CE.EntityID
	--LEFT JOIN
	--OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {P_Contact} WHERE ShortRelationshipName <> "Logon"''') AS PC
	--	ON PC.Entity1ID = LG.Entity1ID AND PC.Entity2ID = LG.Entity2ID
	--WHERE PC.Entity2ID IS NULL

	RETURN 
END